USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetOpportunityProductDetails')
	BEGIN
		DROP  Procedure  dbo.GetOpportunityProductDetails
	END
GO

CREATE Procedure [dbo].[GetOpportunityProductDetails]
	@PKID				varchar(10) = ''
,	@OPPORTUNITYGUID	VARCHAR(100)
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@products		varchar(max)

	SELECT	
		@sql = ''
	,	@products	= dbo.GetProductsXML()

	IF NOT EXISTS (SELECT 1 FROM TR_PRODUCT WHERE ProductID  = @PKID)
		SET @PKID = ''


	;WITH OppProd AS (
		SELECT
			o.ProductID			AS ProductID
		,	o.Quantity			AS Quantity
		,	o.CustomPrice		AS CustomPrice
		,	o.Quantity * ISNULL(o.CustomPrice, p.UnitPrice) AS TOTAL
		,	o.OpportunityID		AS OppProdID
		,	CAST(o.OppGUID AS VARCHAR(40))			AS OpportunityGUID
		FROM TR_OPPORTUNITYI as o WITH (NOLOCK)
		INNER JOIN TR_PRODUCT as p WITH (NOLOCK)
			ON p.ProductID = o.ProductID
		WHERE
			o.OpportunityID = ISNULL(@PKID, 0)
		UNION
		SELECT
			0					AS ProductID
		,	0					AS Quantity
		,	NULL				AS CustomPrice
		,	0					AS OppProdID
		,	0					AS TOTAL
		,	@OPPORTUNITYGUID	AS OpportunityGUID
		WHERE
			ISNULL(@PKID, '') = ''
	), FinalXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 AS ID, @products
		UNION
		SELECT
			3 AS ID, 
		+	'<PKID><![CDATA[' + CAST(o.OppProdID AS VARCHAR) + ']]></PKID>'
		+	'<PRODUCTID><![CDATA[' + CAST(o.ProductID AS VARCHAR) + ']]></PRODUCTID>'
		+	'<QUANTITY><![CDATA[' + CAST(ISNULL(o.QUANTITY,'') AS VARCHAR) + ']]></QUANTITY>'
		+	'<CUSTOMPRICE><![CDATA[' + CAST(ISNULL( CAST(o.CUSTOMPRICE AS VARCHAR),'') AS VARCHAR) + ']]></CUSTOMPRICE>'
		+	'<TOTAL><![CDATA[' + CAST(ISNULL(o.TOTAL,'') AS VARCHAR) + ']]></TOTAL>'
		+	'<OPPORTUNITYPRODID><![CDATA[' + CAST(o.OppProdID AS VARCHAR) + ']]></OPPORTUNITYPRODID>'
		+	'<OPPORTUNITYGUID><![CDATA['+ ISNULL(CAST(o.OpportunityGUID AS VARCHAR(40)),'') +']]></OPPORTUNITYGUID>'
					AS Details
		FROM OppProd as o
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM FinalXML
	Order by ID

	select @sql	AS Detail
END



-- exec GetOpportunityProductDetails